# profileRoom字段
# https://www.huya.com/ + profileRoom字段就是直播间
import pymysql
import requests
'Top10主播的贡献榜（功能13）'


# 获取贡献榜
def getContribution(top10_arr):
    index = 0
    contributionArr = []
    for top_10 in top10_arr:
        # url = "https://www.huya.com/"+profileRoom
        url = 'https://www.huya.com/cache5min.php?m=WeekRank&do=getItemsByPid&pid=' + str(top_10["roomNumber"])
        res = requests.get(url)
        # 转成json格式
        response = res.json()
        # 获取详细老板的信息
        artists = response['data']['vWeekRankItem']
        # 防止混入一些空数据
        if artists is None:
            continue
        for i in artists:
            dict_data = {
                "nick": i["sNickName"],
                "score": i["iScore"],
                "roomNumber": top_10["roomNumber"],
                "number": artists.index(i) + 1
            }
            contributionArr.append(dict_data)
        index = index + 1
    return contributionArr


# 创建MySQL连接函数（当做固定）
def getMysqlConn():
    conn = pymysql.connect(host='106.13.194.7',
                           port=3306,
                           user='rdsroot',
                           password='abc123456',
                           db='huya',
                           charset='utf8')
    # 创建操作的游标
    cursor = conn.cursor()
    # 设置字符编码及自动提交
    cursor.execute('set names utf8')  # 固定格式
    cursor.execute('set autocommit=1')
    # 设置自动提交
    return conn, cursor


# 保存贡献榜数据
def saveContributionData(contributionArr):
    conn, cursor = getMysqlConn()
    # sql语句（注意要改回来，删除copy1）
    sql = "insert into t_contribution (id,roomNumber,nick,score,number) values(REPLACE(UUID(),'-',''),%s,%s,%s,%s)"
    for contribution in contributionArr:
        # 封装成元组进行插入
        add_data = (contribution["roomNumber"], contribution["nick"], contribution["score"], contribution["number"])
        # 这里会把sql语句中的%s替换掉
        cursor.execute(sql, add_data)
    print("mysql插入数据完成")
    # 关闭连接
    cursor.close()
    conn.close()


# 获取数据库中，Top10主播
def getTop10Anchor():
    conn, cursor = getMysqlConn()
    sql = "select * from t_top10Anchor "
    cursor.execute(sql)
    result = cursor.fetchall()
    print("mysql查询数据完成")
    # 关闭连接
    cursor.close()
    conn.close()
    return result


# 修改成为数组形式
def changeToArr(res):
    # 要返回的数组
    arr = []
    for i in res:
        add_dict = {
            "roomNumber": i[1],
            "nick": i[2],
            "totalHot": i[3],
            "title": i[4],
            "areaName": i[5]
        }
        arr.append(add_dict)
    return arr


# 单元测试用
if __name__ == '__main__':
    result = getTop10Anchor()
    top10_arr = changeToArr(result)
    print("已获取Top10主播")
    contributionArr = getContribution(top10_arr)
    print("已获取贡献榜")
    saveContributionData(contributionArr)
